package com.zretc.emptraining.daoimpl;

import java.time.LocalDateTime;

import com.zretc.emptraining.dao.RefTableDao;
import com.zretc.emptraining.dbtools.DBTools;
import com.zretc.emptraining.dbtools.PageData;
import com.zretc.emptraining.entity.RefTable;

public class RefTableDaoImp implements RefTableDao {

	@Override
	public boolean addRefTable(RefTable refTable) {
		int n = 0;
		if (refTable != null) {
			String sql = "insert into reftable value(0,?,?,now())";
			n = DBTools.getUpdate(sql, refTable.getEmpNo(), refTable.getCpNo());
		}
		return n > 0;
	}

	@Override
	public boolean deleteRefTable(int refTableNo) {
		String sql = "delete from reftable where refTableNo = ?";
		int n = DBTools.getUpdate(sql, refTableNo);
		return n > 0;
	}

	@Override
	public boolean updateRefTable(RefTable refTable) {
		int n = 0;
		if (refTable != null) {
			String sql = "update reftable set empNo = ?,cpNo = ?,courseTime = ? where refTableNo = ?";
			n = DBTools.getUpdate(sql, refTable.getEmpNo(), refTable.getCpNo(), LocalDateTime.now(),
					refTable.getRefTableNo());
		}
		return n > 0;
	}

	@Override
	public PageData<RefTable> getRefTabelByEmpNo(int page, int pageSize, int empNo) {
		PageData<RefTable> pd = new PageData<RefTable>();
		String sql = "select reftable.refTableNo,reftable.empNo,(select emp.empName from emp where empNo = reftable.empNo) as eName,reftable.cpNo,(SELECT course.courseName \r\n"
				+ "from course \r\n"
				+ "where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = reftable.cpNo) )\r\n"
				+ "as cName from reftable where empNo =  ? order by courseTime desc";
		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize, empNo);
		return pd;
	}

	@Override
	public PageData<RefTable> getRefTabelBycpNo(int page, int pageSize, int cpNo) {
		PageData<RefTable> pd = new PageData<RefTable>();
//		String sql = "select * from reftable where cpNo = ?";
		String sql = "select reftable.refTableNo,reftable.empNo,(select emp.empName from emp where empNo = reftable.empNo) as eName,reftable.cpNo,(SELECT course.courseName \r\n"
				+ "from course \r\n"
				+ "where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = reftable.cpNo) )\r\n"
				+ "as cName from reftable where cpNo =  ? order by courseTime desc";

		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize, cpNo);
		return pd;
	}

	@Override
	public PageData<RefTable> getRefTabelByPage(int page, int pageSize) {
		PageData<RefTable> pd = new PageData<RefTable>();

		String sql = "select reftable.refTableNo,reftable.empNo,(select emp.empName from emp where empNo = reftable.empNo) as eName,reftable.cpNo,(SELECT course.courseName from course where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = reftable.cpNo) \r\n"
				+ ")as cName from reftable order by courseTime desc";
		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize);

		return pd;
	}

	@Override
	public boolean delRefWhereDelMA(int empNo, int cpNo) {
		String sql = "delete from reftable where empNo = ? and cpNo = ?";
		int n = DBTools.getUpdate(sql, empNo, cpNo);
		return n > 0;
	}

	@Override
	public PageData<RefTable> getRefTabelBydeptNo(int page, int pageSize, int deptNo) {
		String sql = "select r.refTableNo,r.empNo,(select emp.empName from emp where empNo = r.empNo) as eName,r.cpNo,(SELECT course.courseName \r\n"
				+ "from course \r\n"
				+ "where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = r.cpNo) )\r\n"
				+ "as cName from reftable r inner JOIN (SELECT empNo as en FROM emp WHERE deptNo = ? ) e ON e.en=r.empNo order by courseTime desc";
		PageData<RefTable> pd = new PageData<RefTable>();
		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize, deptNo);
		return pd;
	}

	@Override
	public PageData<RefTable> getRefTabelBydeptNoWithEmpNo(int page, int pageSize, int deptNo, int empNo) {
		String sql = "select r.refTableNo,r.empNo,(select emp.empName from emp where empNo = r.empNo) as eName,r.cpNo,(SELECT course.courseName \r\n"
				+ "from course \r\n"
				+ "where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = r.cpNo) )\r\n"
				+ "as cName from reftable r inner JOIN (SELECT empNo as en FROM emp WHERE deptNo = ?  and empNo = ? ) e ON e.en=r.empNo order by courseTime desc";
		PageData<RefTable> pd = new PageData<RefTable>();
		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize, deptNo, empNo);
		return pd;
	}

	@Override
	public boolean diae(int cpNo, int empNo) {
		// TODO Auto-generated method stub
		String sql = "select count(refTableNo) as count from refTable where cpNo = ? and empNo = ?";
		int page = 1;
		int pageSize = 1;
		PageData<RefTable> pd = new PageData<RefTable>();
		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize, cpNo, empNo);
		if (pd.getData().get(0).getCount() == 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public int getCountByCPNo(int cpNo) {
		// TODO Auto-generated method stub
		String sql = "select count(refTableNo) as count from refTable where cpNo = ?";
		int page = 1;
		int pageSize = 1;
		PageData<RefTable> pd = new PageData<RefTable>();
		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize, cpNo);
		int n = pd.getData().get(0).getCount();
		return n;

	}

	@Override
	public PageData<RefTable> getRefTabelBycpNoWithEmpNo(int page, int pageSize, int cpNo, int empNo) {
		String sql = "select reftable.refTableNo, reftable.empNo,(select emp.empName from emp where empNo = reftable.empNo) as eName,reftable.cpNo,(SELECT course.courseName from course where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = reftable.cpNo) )as cName \r\n"
				+ "from reftable where cpNo =  ? and reftable.empNo = ?";
		PageData<RefTable> pd = new PageData<RefTable>();
		pd = DBTools.exQueryByPage(sql, RefTable.class, page, pageSize, cpNo, empNo);
		return pd;
	}

}
